USE ConsultTracking
go
drop proc CreateLocation
drop proc UpdateLocation
drop proc GetLocationsByCustomerID
GO
-------------------------------------------------
create proc CreateLocation
@CustomerID int,
@LocationName nvarchar(50),
@Address1 nvarchar(500),
@Address2 nvarchar(50),
@City nvarchar(100),
@State nvarchar(30),
@Zip nvarchar(10),
@PurposeTypeID int
as
INSERT INTO [ConsultTracking].[dbo].[Location]
           ([CustomerID]
           ,[LocationName]
           ,[Address1]
           ,[Address2]
           ,[City]
           ,[State]
           ,[Zip])
     VALUES
           (@CustomerID
           ,@LocationName
           ,@Address1
           ,@Address2
           ,@City
           ,@State
           ,@Zip)

declare @LastLocationID int;
set @LastLocationID = (select max(LocationID) from Location)

INSERT INTO [ConsultTracking].[dbo].[LocationPurpose]
           ([LocationID]
           ,[PurposeTypeID])
     VALUES
           (@LastLocationID
           ,@PurposeTypeID)

-----------------------------------------------------------
go
create proc UpdateLocation
@LocationID int,
@LocationName nvarchar(50),
@Address1 nvarchar(500),
@Address2 nvarchar(50),
@City nvarchar(100),
@State nvarchar(30),
@Zip nvarchar(10)
as
UPDATE [ConsultTracking].[dbo].[Location]
   SET [LocationName] = @LocationName
      ,[Address1] = @Address1
      ,[Address2] = @Address2
      ,[City] = @City
      ,[State] = @State
      ,[Zip] = @Zip
 	  ,[Modified] = GETDATE()
WHERE LocationID = @LocationID
------------------------------------------------------------
go
create proc GetLocationsByCustomerID
@CustomerID int
as
SELECT [LocationID]
      ,[LocationName]
      ,[Address1]
      ,[Address2]
      ,[City]
      ,[State]
      ,[Zip]
  FROM [ConsultTracking].[dbo].[Location]
where CustomerID = @CustomerID
